Go to the documentation of this file.
00001 
00027 using System;
00028 using System.Collections.Generic;
00029 using System.Text;
00030 using RAIS.Common.DynamicMaskManagement;
00031 using System.Data;
00032 using System.Data.SqlClient;
00033 using RAIS.Common.TableManagement;
00034 using System.Xml;
00035 using System.IO;
00036 
00037 namespace RAIS.DataAccessLayer
00038 {
00039     public class SpecificReportManagement
00040     {
00041         #region PUBLIC METHODS
00042         public static Dictionary<string, string> GetSubcategories(string category)
00043         {
00044             int catID = 5;
00045             if (category.Equals("Statistics"))
00046                 catID = 6;
00047             Dictionary<string, string> result = new Dictionary<string, string>();
00048             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00049             {
00050                 DataTable dt = new DataTable();
00051                 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT [PK Subcategory ID], [Subcategory Name] FROM Subcategory WHERE [FK Category ID] = " + catID, con);
00052                 dAdapt.Fill(dt);
00053                 foreach (DataRow row in dt.Rows)
00054                 {
00055                     result.Add(row["PK Subcategory ID"].ToString(), row["Subcategory Name"].ToString());
00056                 }
00057             }
00058             return result;
00059         }
00060         public static bool AddSpecificReport(string specificReportName, string fileName, string reportDefinition, ref int reportID)
00061         {
00062             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00063             {
00064                 con.Open();
00065                 SqlCommand cmd = new SqlCommand("INSERT INTO [Specific Report]([Specific Report Name], [File Name], [Specific Report Definition]) VALUES ('" + specificReportName + "', '" + fileName + "', '')", con);
00066                 cmd.ExecuteNonQuery();
00067                 cmd = new SqlCommand("SELECT [PK Specific Report ID] FROM [Specific Report] WHERE [PK Specific Report ID] = (SELECT MAX([PK Specific Report ID]) FROM [Specific Report])", con);
00068                 SqlDataReader reader = cmd.ExecuteReader();
00069                 reader.Read();
00070                 reportID = int.Parse(reader.GetValue(0).ToString());
00071                 reader.Close();
00072                 con.Close();
00073                 addSpecificReportID(reportID);
00074                 bool result = createReportDefinition(reportID, reportDefinition);
00075                 return result;
00076             }
00077         }
00078         public static Dictionary<string, string> GetReports()
00079         {
00080             Dictionary<string, string> result = new Dictionary<string, string>();
00081             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00082             {
00083                 DataTable dt = new DataTable();
00084                 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Specific Report]", con);
00085                 dAdapt.Fill(dt);
00086                 foreach (DataRow row in dt.Rows)
00087                 {
00088                     result.Add(row[0].ToString(), row[1].ToString());
00089                 }
00090             }
00091             return result;
00092         }
00093         public static DataTable GetReportByID(int id)
00094         {
00095             DataTable dt = new DataTable();
00096             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00097             {
00098                 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT sr.[PK Specific Report ID], sr.[Specific Report Name], " +
00099                                                             "sr.[File Name], dm.[Query Name], sc.[FK Category ID], c.[Category Name], m.[FK Subcategory ID] " +
00100                                                             "FROM [Specific Report] AS sr " +
00101                                                             "INNER JOIN [Dynamic Mask] AS dm ON sr.[PK Specific Report ID] = dm.[FK Specific Report ID] " +
00102                                                             "INNER JOIN Mask AS m ON dm.[FK Mask ID] = m.[PK Mask ID] " +
00103                                                             "INNER JOIN Subcategory AS sc ON sc.[PK Subcategory ID] = m.[FK Subcategory ID] " +
00104                                                             "INNER JOIN Category c ON c.[PK Category ID] = sc.[FK Category ID] " +
00105                                                             "WHERE sr.[PK Specific Report ID] = " + id, con);
00106                 dAdapt.Fill(dt);
00107             }
00108             return dt;
00109         }
00110         public static bool UpdateSpecificReport(int specificReportID, int maskID, int dynamicMaskID, int subcategoryID, string specificReportName, string fileName, string reportDefinition)
00111         {
00112             bool result = true;
00113             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00114             {
00115                 con.Open();
00116                 if (reportDefinition != null)
00117                 {
00118                     result = createReportDefinition(specificReportID, reportDefinition);
00119                     if (result)
00120                     {
00121                         string selStr = "UPDATE [Specific Report] SET [Specific Report Name] = '" + specificReportName + "', [File Name] = '" + fileName + "' WHERE [PK Specific Report ID] = " + specificReportID;
00122                         SqlCommand cmdUpdSpecificReport = new SqlCommand(selStr, con);
00123                         cmdUpdSpecificReport.ExecuteNonQuery();
00124                     }
00125                 }
00126                 else
00127                 {
00128                     string selStr = "UPDATE [Specific Report] SET [Specific Report Name] = '" + specificReportName + "' WHERE [PK Specific Report ID] = " + specificReportID;
00129                     SqlCommand cmdUpdSpecificReport = new SqlCommand(selStr, con);
00130                     cmdUpdSpecificReport.ExecuteNonQuery();
00131                 }
00132 
00133                 if (result)
00134                 {
00135                     SqlCommand cmdUpdMask = new SqlCommand("UPDATE Mask SET [Mask Name] = '" + specificReportName + "', [FK Subcategory ID] = " + subcategoryID + " WHERE [PK Mask ID] = " + maskID, con);
00136                     cmdUpdMask.ExecuteNonQuery();
00137                     SqlCommand cmdUpdDynamicMask = new SqlCommand("UPDATE [Dynamic Mask] SET [Dynamic Mask Name] = '" + specificReportName + "' WHERE [PK Dynamic Mask ID] = " + dynamicMaskID, con);
00138                     cmdUpdDynamicMask.ExecuteNonQuery();
00139                     con.Close();
00140                 }
00141             }
00142             return result;
00143         }
00144         public static int GetSubcategoryIDByReportID(int id)
00145         {
00146             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00147             {
00148                 SqlCommand cmd = new SqlCommand("SELECT m.[FK Subcategory ID] " +
00149                                                 "FROM [Specific Report] AS sr " +
00150                                                 "INNER JOIN [Dynamic Mask] AS dm ON sr.[PK Specific Report ID] = dm.[FK Specific Report ID] " +
00151                                                 "INNER JOIN Mask AS m ON dm.[FK Mask ID] = m.[PK Mask ID] " +
00152                                                 "WHERE sr.[PK Specific Report ID] = " + id, con);
00153                 con.Open();
00154                 SqlDataReader reader = cmd.ExecuteReader();
00155                 reader.Read();
00156                 int result = int.Parse(reader.GetValue(0).ToString());
00157                 reader.Close();
00158                 con.Close();
00159                 return result;
00160             }
00161         }
00162         public static string GetSubcategoryNameByReportID(int specificReportID)
00163         {
00164             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00165             {
00166                 SqlCommand cmd = new SqlCommand("SELECT sc.[Subcategory Name] " +
00167                                                 "FROM [Specific Report] AS sr " +
00168                                                 "INNER JOIN [Dynamic Mask] AS dm ON sr.[PK Specific Report ID] = dm.[FK Specific Report ID] " +
00169                                                 "INNER JOIN Mask AS m ON dm.[FK Mask ID] = m.[PK Mask ID] " +
00170                                                 "INNER JOIN Subcategory sc ON sc.[PK Subcategory ID] = m.[FK Subcategory ID] " +
00171                                                 "WHERE sr.[PK Specific Report ID] = " + specificReportID, con);
00172                 con.Open();
00173                 SqlDataReader reader = cmd.ExecuteReader();
00174                 reader.Read();
00175                 string result = reader.GetValue(0).ToString();
00176                 reader.Close();
00177                 con.Close();
00178                 return result;
00179             }
00180         }
00181         public static bool RemoveSpecificReport(int id, ref string returnMessage)
00182         {
00183             try
00184             {
00185                 using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00186                 {
00187                     con.Open();
00188                     SqlCommand cmd = new SqlCommand("DELETE FROM [Mask] WHERE [PK Mask ID] = " + getMaskID(id), con);
00189                     cmd.ExecuteNonQuery();
00190                     cmd = new SqlCommand("DELETE FROM [Dynamic Mask] WHERE [FK Specific Report ID] = " + id, con);
00191                     cmd.ExecuteNonQuery();
00192                     cmd = new SqlCommand("DELETE FROM [Specific Report] WHERE [PK Specific Report ID] = " + id, con);
00193                     cmd.ExecuteNonQuery();
00194                     con.Close();
00195                 }
00196                 return true;
00197             }
00198             catch (Exception ex)
00199             {
00200                 returnMessage = ex.ToString();
00201                 return false;
00202             }
00203         }
00204         public static byte[] GetSpecificReportFile(int specificReportID)
00205         {
00206             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00207             {
00208                 SqlCommand cmd = new SqlCommand("SELECT [Specific Report Definition] FROM [Specific Report] WHERE [PK Specific Report ID] = " + specificReportID, con);
00209                 con.Open();
00210                 SqlDataReader reader = cmd.ExecuteReader();
00211                 reader.Read();
00212                 System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
00213                 byte[] buffer = enc.GetBytes(reader.GetValue(0).ToString());
00214                 reader.Close();
00215                 con.Close();
00216                 return buffer;
00217             }
00218         }
00219         public static string GetSpecificReportDefinition(int specificReportID)
00220         {
00221             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00222             {
00223                 SqlCommand cmd = new SqlCommand("SELECT [Specific Report Definition] FROM [Specific Report] WHERE [PK Specific Report ID] = " + specificReportID, con);
00224                 con.Open();
00225                 SqlDataReader reader = cmd.ExecuteReader();
00226                 reader.Read();
00227                 string result = reader.GetValue(0).ToString();
00228                 reader.Close();
00229                 con.Close();
00230                 return result;
00231             }
00232         }
00233         public static List<string> GetQueryFieldsByID(int specificReportID)
00234         {
00235             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00236             {
00237                 List<string> result = new List<string>();
00238                 SqlCommand cmd = new SqlCommand("SELECT [Query Name] " +
00239                                                 "FROM [Specific Report] sr " +
00240                                                 "INNER JOIN [Dynamic Mask] dm ON sr.[PK Specific Report ID] = dm.[FK Specific Report ID] " +
00241                                                 "INNER JOIN Mask m ON m.[PK mask id] = dm.[fk mask id] " +
00242                                                 "WHERE sr.[PK Specific Report ID] = " + specificReportID, con);
00243                 con.Open();
00244                 SqlDataReader reader = cmd.ExecuteReader();
00245                 reader.Read();
00246                 string query = reader.GetValue(0).ToString();
00247                 reader.Close();
00248                 con.Close();
00249                 string param = String.Empty;
00250                 for (int i = 0; i < getNumOfParameter(query); i++)
00251                 {
00252                     param += "NULL, ";
00253                 }
00254                 if (param.Length > 1)
00255                     param = param.Remove(param.Length - 2);
00256                 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [" + query + "](" + param + ")", con);
00257                 DataTable dt = new DataTable();
00258                 dAdapt.Fill(dt);
00259                 foreach (DataColumn col in dt.Columns)
00260                 {
00261                     result.Add(col.ColumnName);
00262                 }
00263                 return result;
00264             }
00265         }
00266         public static void SaveReportDefinition(int specificReportID, string reportDefinition)
00267         {
00268             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00269             {
00270                 SqlCommand cmd = new SqlCommand("UPDATE [Specific Report] " +
00271                                                 "SET [Specific Report Definition] = '" + reportDefinition + "' " +
00272                                                 "WHERE [PK Specific Report ID] = " + specificReportID, con);
00273                 con.Open();
00274                 cmd.ExecuteNonQuery();
00275                 con.Close();
00276             }
00277         }
00278         public static bool IsSpecificReport(int dynamicMaskID)
00279         {
00280             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00281             {
00282                 SqlCommand cmd = new SqlCommand("SELECT [FK Specific Report ID] FROM [Dynamic Mask] WHERE [PK Dynamic Mask ID] = " + dynamicMaskID, con);
00283                 con.Open();
00284                 SqlDataReader reader = cmd.ExecuteReader();
00285                 reader.Read();
00286                 string value = reader.GetValue(0).ToString();
00287                 reader.Close();
00288                 con.Close();
00289                 if (value != null && !value.ToString().Equals(String.Empty))
00290                     return true;
00291                 else return false;
00292             }
00293         }
00294         public static int GetSpecificReportID(int dynamicMaskID)
00295         {
00296             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00297             {
00298                 SqlCommand cmd = new SqlCommand("SELECT [FK Specific Report ID] FROM [Dynamic Mask] WHERE [PK Dynamic Mask ID] = " + dynamicMaskID, con);
00299                 con.Open();
00300                 SqlDataReader reader = cmd.ExecuteReader();
00301                 reader.Read();
00302                 int value = int.Parse(reader.GetValue(0).ToString());
00303                 reader.Close();
00304                 con.Close();
00305                 return value;
00306             }
00307         }
00308         public static DataTable GetQueryResult(string queryName, string parameter, string grouping, RAIS.Common.UserManagement.UserData currentUser, int dynamicMaskID)
00309         {
00310             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00311             {
00312                 string query = "SELECT * FROM [" + queryName + "]" + parameter;
00313                 SqlDataAdapter dAdapt = new SqlDataAdapter(query, con);
00314                 DataTable dt = new DataTable();
00315                 dAdapt.Fill(dt);
00316 
00317                 string restrictions = String.Empty;
00318                 if (DataRoleRestrictionManagement.IsDataRoleRestricted(dt, currentUser, dynamicMaskID, ref restrictions))
00319                 {
00320                     DataRoleRestrictionManagement.ApplyDataRoleRestrictions(queryName, parameter, grouping, restrictions, dt, currentUser, dynamicMaskID);
00321                 }
00322                 else if (!grouping.Equals(String.Empty))
00323                 {
00324                     query = "SELECT [" + grouping + "] FROM [" + queryName + "]" + parameter + " GROUP BY [" + grouping + "]";
00325                     dAdapt = new SqlDataAdapter(query, con);
00326                     dAdapt.Fill(dt);
00327                 }
00328 
00329                 return dt;
00330             }
00331         }
00332         public static DataTable GetDataTableFromQuery(string query)
00333         {
00334             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00335             {
00336                 SqlDataAdapter dAdapt = new SqlDataAdapter(query, con);
00337                 DataTable dt = new DataTable();
00338                 dAdapt.Fill(dt);
00339                 return dt;
00340             }
00341         }
00342         public static List<string> GetSubreports(int specificReportID)
00343         {
00344             List<string> result = new List<string>();
00345             XmlDocument xmlReport = new XmlDocument();
00346             StringReader reader = new StringReader(GetSpecificReportDefinition(specificReportID));
00347             xmlReport.Load(reader);
00348             XmlNamespaceManager manager = new XmlNamespaceManager(xmlReport.NameTable);
00349             manager.AddNamespace("def", xmlReport.DocumentElement.NamespaceURI);
00350             foreach (XmlNode node in xmlReport.SelectNodes("//def:Subreport/def:ReportName", manager))
00351             {
00352                 result.Add(node.InnerText);
00353             }
00354             return result;
00355         }
00356         public static int GetMaskIdByReportName(string specificReportName)
00357         {
00358             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00359             {
00360                 SqlCommand cmd = new SqlCommand("SELECT [PK Mask ID] " +
00361                                                 "FROM Mask m INNER JOIN [Dynamic Mask] dm ON dm.[FK Mask ID] = m.[PK Mask ID] " +
00362                                                 "INNER JOIN [Specific Report] sr ON sr.[PK Specific Report ID] = dm.[FK Specific Report ID] " +
00363                                                 "WHERE [Specific Report Name] = '" + specificReportName + "'", con);
00364                 con.Open();
00365                 SqlDataReader reader = cmd.ExecuteReader();
00366                 reader.Read();
00367                 int result = int.Parse(reader.GetValue(0).ToString());
00368                 reader.Close();
00369                 con.Close();
00370                 return result;
00371             }
00372         }
00373         public static int GetSpecificReportIdByName(string specificReportName)
00374         {
00375             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00376             {
00377                 SqlCommand cmd = new SqlCommand("SELECT [PK Specific Report ID] FROM [Specific Report] WHERE [Specific Report Name] = '" + specificReportName + "'", con);
00378                 con.Open();
00379                 SqlDataReader reader = cmd.ExecuteReader();
00380                 reader.Read();
00381                 int result = int.Parse(reader.GetValue(0).ToString());
00382                 reader.Close();
00383                 con.Close();
00384                 return result;
00385             }
00386         }
00387         public static string GetSpecificReportNameById(int specificReportID)
00388         {
00389             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00390             {
00391                 SqlCommand cmd = new SqlCommand("SELECT [Specific Report Name] FROM [Specific Report] WHERE [PK Specific Report ID] = " + specificReportID, con);
00392                 con.Open();
00393                 SqlDataReader reader = cmd.ExecuteReader();
00394                 reader.Read();
00395                 string result = reader.GetValue(0).ToString();
00396                 reader.Close();
00397                 con.Close();
00398                 return result;
00399             }
00400         }
00401         public static RAIS.Common.UserManagement.Mask GetMaskByReportName(string specificReportName)
00402         {
00403             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00404             {
00405                 SqlCommand cmd = new SqlCommand("SELECT c.[PK Category ID], c.[Category Name], s.[PK Subcategory ID], s.[Subcategory Name]" +
00406                                                 ", s.[Custom Subcategory], s.[Order], s.[Image Index], m.[PK Mask ID], m.[Mask Name]" +
00407                                                         ", CASE WHEN m.[FK Dynamic Mask ID] IS NULL THEN m.Reference ELSE dmt.[Reference] END AS Reference" +
00408                                                 ", dm.[PK Dynamic Mask ID] " +
00409                                                 "FROM [Mask] m " +
00410                                                 "JOIN [Subcategory] s ON s.[PK Subcategory ID] = m.[FK Subcategory ID] " +
00411                                                 "JOIN [Category] c ON c.[PK Category ID] = s.[FK Category ID] " +
00412                                                 "LEFT JOIN [Dynamic Mask] dm ON dm.[FK Mask ID] = m.[PK Mask ID] AND dm.[Order] = 0 " +
00413                                                 "LEFT JOIN [Dynamic Mask Type] dmt ON dmt.[PK Dynamic Mask Type ID] = dm.[FK Dynamic Mask Type ID] " + 
00414                                                 "WHERE [PK Mask ID] = " + GetMaskIdByReportName(specificReportName), con);
00415                 con.Open();
00416                 SqlDataReader sqlReader = cmd.ExecuteReader();
00417                 sqlReader.Read();
00418                 RAIS.Common.UserManagement.Mask mask = new RAIS.Common.UserManagement.Mask(sqlReader);
00419                 con.Close();
00420                 return mask;
00421             }
00422         }
00423         public static void SetQuery(int dynamicMaskID, string queryName)
00424         {
00425             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00426             {
00427                 SqlCommand cmd = new SqlCommand("UPDATE [Dynamic Mask] SET [Query Name] = '" + queryName + "' WHERE [PK Dynamic Mask ID] = " + dynamicMaskID, con);
00428                 con.Open();
00429                 cmd.ExecuteNonQuery();
00430                 con.Close();
00431             }
00432         }
00433         public static bool IsSubreport(string specificReportName)
00434         {
00435             foreach (string id in GetReports().Keys)
00436             {
00437                 int specificReportID = Convert.ToInt32(id);
00438                 foreach (string subreport in GetSubreports(specificReportID))
00439                 {
00440                     if (subreport.Equals(specificReportName))
00441                         return true;
00442                 }
00443             }
00444             return false;
00445         }
00446         public static void GenerateMenuItem(int specificReportID, bool generateMenuItem)
00447         {
00448             int hidden = 0;
00449             if (!generateMenuItem)
00450                 hidden = 1;
00451             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00452             {
00453                 int maskID = getMaskID(specificReportID);
00454                 SqlCommand cmd = new SqlCommand("UPDATE Mask " + 
00455                                                 "SET [Hidden] = " + hidden + " " +
00456                                                 "WHERE [PK Mask ID] = " + maskID, con);
00457                 con.Open();
00458                 cmd.ExecuteNonQuery();
00459                 con.Close();
00460             }
00461         }
00462         public static bool IsMenuItemGenerated(int specificReportID)
00463         {
00464             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00465             {
00466                 int maskID = getMaskID(specificReportID);
00467                 SqlCommand cmd = new SqlCommand("SELECT m.[Hidden] " +
00468                                                 "FROM Mask m " + 
00469                                                 "WHERE m.[PK Mask ID] = " + maskID, con);
00470                 con.Open();
00471                 SqlDataReader reader = cmd.ExecuteReader();
00472                 reader.Read();
00473                 int result = Convert.ToInt32(reader.GetValue(0));
00474                 reader.Close();
00475                 con.Close();
00476 
00477                 if (result == 0)
00478                     return true;
00479                 else return false;
00480             }
00481         }
00482         #endregion
00483 
00484         #region PRIVATE METHODS
00485         private static int getNumOfParameter(string query)
00486         {
00487             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00488             {
00489                 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Query Parameter] qp " +
00490                                                         "INNER JOIN Query q ON q.[PK Query ID] = qp.[FK Query ID] " +
00491                                                         "WHERE q.[Query Name] = '" + query + "'", con);
00492                 DataTable dt = new DataTable();
00493                 dAdapt.Fill(dt);
00494                 return dt.Rows.Count;
00495             }
00496         }
00497         private static int getMaskID(int specificReportID)
00498         {
00499             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00500             {
00501                 SqlCommand cmd = new SqlCommand("SELECT [FK Mask ID] FROM [Dynamic Mask] WHERE [FK Specific Report ID] = " + specificReportID, con);
00502                 con.Open();
00503                 SqlDataReader reader = cmd.ExecuteReader();
00504                 reader.Read();
00505                 int id = int.Parse(reader.GetValue(0).ToString());
00506                 con.Close();
00507                 return id;
00508             }
00509         }
00510         private static void addSpecificReportID(int specificReportID)
00511         {
00512             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00513             {
00514                 SqlCommand cmd = new SqlCommand("UPDATE [Dynamic Mask] SET [FK Specific Report ID] = " + specificReportID + " " +
00515                                                 "WHERE [PK Dynamic Mask ID] = (SELECT MAX([PK Dynamic Mask ID]) " +
00516                                                 "FROM [Dynamic Mask])", con);
00517                 con.Open();
00518                 cmd.ExecuteNonQuery();
00519                 con.Close();
00520             }
00521         }
00522         /*private static void createReportDefinition(int maskID, string reportPath)
00523         {
00524             XmlDocument xmlReport = new XmlDocument();
00525             xmlReport.Load(reportPath);
00526 
00527             #region REMOVE EXISTING DATASOURCES/DATASETS
00528             foreach (XmlNode node in xmlReport.DocumentElement.GetElementsByTagName("DataSources"))
00529             {
00530                 xmlReport.DocumentElement.RemoveChild(node);
00531                 break;
00532             }
00533             foreach (XmlNode node in xmlReport.DocumentElement.GetElementsByTagName("DataSets"))
00534             {
00535                 xmlReport.DocumentElement.RemoveChild(node);
00536                 break;
00537             }
00538             #endregion
00539 
00540             #region WRITE DATASOURCES TO REPORT
00541             // Create Nodes
00542             XmlNode dataSources = xmlReport.CreateElement("DataSources", xmlReport.DocumentElement.NamespaceURI);
00543             XmlNode dataSource = xmlReport.CreateElement("DataSource", dataSources.NamespaceURI);
00544             XmlAttribute dataSourceNameAtt = xmlReport.CreateAttribute("Name");
00545             dataSourceNameAtt.Value = "ReportDataSource";
00546             XmlNode connProperties = xmlReport.CreateElement("ConnectionProperties", dataSource.NamespaceURI);
00547             XmlNode dataProvider = xmlReport.CreateElement("DataProvider", connProperties.NamespaceURI);
00548             dataProvider.InnerText = "SQL";
00549             XmlNode connString = xmlReport.CreateElement("ConnectString", connProperties.NamespaceURI);
00550 
00551             // Append nodes/attributes
00552             connProperties.AppendChild(dataProvider);
00553             connProperties.AppendChild(connString);
00554             dataSource.AppendChild(connProperties);
00555             dataSource.Attributes.Append(dataSourceNameAtt);
00556             dataSources.AppendChild(dataSource);
00557             xmlReport.DocumentElement.InsertBefore(dataSources, xmlReport.DocumentElement.FirstChild);
00558             #endregion
00559 
00560             #region WRITE DATASETS TO THE REPORT
00561             // Create Nodes
00562             XmlNode dataSets = xmlReport.CreateElement("DataSets", xmlReport.DocumentElement.NamespaceURI);
00563             XmlNode dataSet = xmlReport.CreateElement("DataSet", dataSets.NamespaceURI);
00564             XmlAttribute dataSetName = xmlReport.CreateAttribute("Name");
00565             dataSetName.Value = "ReportDataSet";
00566             XmlNode fields = xmlReport.CreateElement("Fields", dataSet.NamespaceURI);
00567             XmlNode query = xmlReport.CreateElement("Query", dataSet.NamespaceURI);
00568             XmlNode dataSourceName = xmlReport.CreateElement("DataSourceName", query.NamespaceURI);
00569             dataSourceName.InnerText = "ReportDataSource";
00570             XmlNode cmdText = xmlReport.CreateElement("CommandText", query.NamespaceURI);
00571             cmdText.InnerText = "SELECT * FROM ReportDataTable";
00572 
00573             // Create field-nodes
00574             List<string> maskFields = GetMaskFields(maskID);
00575             foreach (string maskField in maskFields)
00576             {
00577                 XmlNode field = xmlReport.CreateElement("Field", fields.NamespaceURI);
00578                 XmlAttribute fieldName = xmlReport.CreateAttribute("Name");
00579                 fieldName.Value = maskField.Replace(' ', '_');
00580                 XmlNode dataField = xmlReport.CreateElement("DataField", field.NamespaceURI);
00581                 dataField.InnerText = maskField;
00582 
00583                 // Append field-nodes
00584                 field.Attributes.Append(fieldName);
00585                 field.AppendChild(dataField);
00586                 fields.AppendChild(field);
00587             }
00588 
00589             // Append nodes/attributes
00590             query.AppendChild(dataSourceName);
00591             query.AppendChild(cmdText);
00592             dataSet.Attributes.Append(dataSetName);
00593             dataSet.AppendChild(fields);
00594             dataSet.AppendChild(query);
00595             dataSets.AppendChild(dataSet);
00596             xmlReport.DocumentElement.InsertAfter(dataSets, xmlReport.DocumentElement.FirstChild);
00597             #endregion
00598 
00599             xmlReport.Save(reportPath);
00600         }*/
00601         private static bool createReportDefinition(int reportID, string reportDefinition)
00602         {
00603             try
00604             {
00605                 XmlDocument xmlReport = new XmlDocument();
00606                 xmlReport.LoadXml(reportDefinition);
00607 
00608                 #region REMOVE EXISTING DATASOURCES/DATASETS
00609                 foreach (XmlNode node in xmlReport.DocumentElement.GetElementsByTagName("DataSources"))
00610                 {
00611                     xmlReport.DocumentElement.RemoveChild(node);
00612                     break;
00613                 }
00614                 foreach (XmlNode node in xmlReport.DocumentElement.GetElementsByTagName("DataSets"))
00615                 {
00616                     xmlReport.DocumentElement.RemoveChild(node);
00617                     break;
00618                 }
00619                 #endregion
00620 
00621                 #region WRITE DATASOURCES TO REPORT
00622                 // Create Nodes
00623                 XmlNode dataSources = xmlReport.CreateElement("DataSources", xmlReport.DocumentElement.NamespaceURI);
00624                 XmlNode dataSource = xmlReport.CreateElement("DataSource", dataSources.NamespaceURI);
00625                 XmlAttribute dataSourceNameAtt = xmlReport.CreateAttribute("Name");
00626                 dataSourceNameAtt.Value = "ReportDataSource";
00627                 XmlNode connProperties = xmlReport.CreateElement("ConnectionProperties", dataSource.NamespaceURI);
00628                 XmlNode dataProvider = xmlReport.CreateElement("DataProvider", connProperties.NamespaceURI);
00629                 dataProvider.InnerText = "SQL";
00630                 XmlNode connString = xmlReport.CreateElement("ConnectString", connProperties.NamespaceURI);
00631 
00632                 // Append nodes/attributes
00633                 connProperties.AppendChild(dataProvider);
00634                 connProperties.AppendChild(connString);
00635                 dataSource.AppendChild(connProperties);
00636                 dataSource.Attributes.Append(dataSourceNameAtt);
00637                 dataSources.AppendChild(dataSource);
00638                 xmlReport.DocumentElement.InsertBefore(dataSources, xmlReport.DocumentElement.FirstChild);
00639                 #endregion
00640 
00641                 #region WRITE DATASETS TO THE REPORT
00642                 // Create Nodes
00643                 XmlNode dataSets = xmlReport.CreateElement("DataSets", xmlReport.DocumentElement.NamespaceURI);
00644                 XmlNode dataSet = xmlReport.CreateElement("DataSet", dataSets.NamespaceURI);
00645                 XmlAttribute dataSetName = xmlReport.CreateAttribute("Name");
00646                 dataSetName.Value = "ReportDataSet";
00647                 XmlNode fields = xmlReport.CreateElement("Fields", dataSet.NamespaceURI);
00648                 XmlNode query = xmlReport.CreateElement("Query", dataSet.NamespaceURI);
00649                 XmlNode dataSourceName = xmlReport.CreateElement("DataSourceName", query.NamespaceURI);
00650                 dataSourceName.InnerText = "ReportDataSource";
00651                 XmlNode cmdText = xmlReport.CreateElement("CommandText", query.NamespaceURI);
00652                 cmdText.InnerText = "SELECT * FROM ReportDataTable";
00653 
00654                 // Create field-nodes
00655                 List<string> queryFields = GetQueryFieldsByID(reportID);
00656                 foreach (string queryField in queryFields)
00657                 {
00658                     XmlNode field = xmlReport.CreateElement("Field", fields.NamespaceURI);
00659                     XmlAttribute fieldName = xmlReport.CreateAttribute("Name");
00660                     fieldName.Value = queryField.Replace(' ', '_');
00661                     XmlNode dataField = xmlReport.CreateElement("DataField", field.NamespaceURI);
00662                     dataField.InnerText = queryField;
00663 
00664                     // Append field-nodes
00665                     field.Attributes.Append(fieldName);
00666                     field.AppendChild(dataField);
00667                     fields.AppendChild(field);
00668                 }
00669 
00670                 // Append nodes/attributes
00671                 query.AppendChild(dataSourceName);
00672                 query.AppendChild(cmdText);
00673                 dataSet.Attributes.Append(dataSetName);
00674                 dataSet.AppendChild(fields);
00675                 dataSet.AppendChild(query);
00676                 dataSets.AppendChild(dataSet);
00677                 xmlReport.DocumentElement.InsertAfter(dataSets, xmlReport.DocumentElement.FirstChild);
00678                 #endregion
00679 
00680                 StringWriter sw = new StringWriter();
00681                 XmlTextWriter xw = new XmlTextWriter(sw);
00682                 xmlReport.WriteTo(xw);
00683                 reportDefinition = sw.ToString();
00684                 SaveReportDefinition(reportID, reportDefinition);
00685                 return true;
00686             }
00687             catch (Exception ex)
00688             {
00689                 return false;
00690             }
00691         }
00692         private static DataTable getValuesByMask(DynamicMask mask, int rowID)
00693         {
00694             List<string> colNames = new List<string>();
00695             string selStr = "SELECT ";
00696 
00697             foreach (KeyValuePair<string, Field> pair in mask.Table.Fields)
00698             {
00699                 Field field = pair.Value;
00700                 if (!colNames.Contains(field.Name))
00701                 {
00702                     colNames.Add(field.Name);
00703                     if (field.IsLookupField && field.Type == Field.FieldType.SingleLookup)
00704                     {
00705                         field.RelatedTable.Fields = TableManagement.GetFieldsByTable(field.RelatedTable);
00706                         if (containsFK(field.RelatedTable.Name, mask.Table.ForeignKeyFieldName))
00707                         {
00708                             selStr += "(SELECT \"" + field.RelatedTable.VisibleTextFieldName + "\" FROM \"" + field.RelatedTable.Name + "\" WHERE \"" + mask.Table.ForeignKeyFieldName + "\" = " + rowID + ") AS \"" + field.Name + "\", ";
00709                         }
00710                         else
00711                         {
00712                             selStr += "(SELECT \"" + field.RelatedTable.VisibleTextFieldName + "\" FROM \"" + field.RelatedTable.Name + "\" WHERE \"" + field.RelatedTable.PrimaryKeyFieldName + "\" = (SELECT \"" + field.RelatedTable.ForeignKeyFieldName + "\" FROM \"" + mask.Table.Name + "\" WHERE \"" + mask.Table.PrimaryKeyFieldName + "\" = " + rowID + ")) AS \"" + field.Name + "\", ";
00713                         }
00714                     }
00715                     else if (field.Type != Field.FieldType.MultipleLookup)
00716                     {
00717                         selStr += "\"" + field.Name + "\", ";
00718                     }
00719                     if (field.Type == Field.FieldType.MultipleLookup)
00720                     {
00721                         //LBLresult.Text += field.RelatedTable.Name + ", " + field.RelatedTable.MultipleLookupForeignKey + ", " + field.Table.MultipleLookupForeignKey + ", " + field.Table.HistoryTableName + ", " + field.RelatedTable.HistoryTableName + "; ";
00722                     }
00723                 }
00724             }
00725             selStr = selStr.Remove(selStr.Length - 2);
00726             selStr += " FROM \"" + mask.Table.Name + "\" WHERE \"" + mask.Table.PrimaryKeyFieldName + "\" = " + rowID;
00727             SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString);
00728             SqlDataAdapter dAdapt = new SqlDataAdapter(selStr, con);
00729             DataTable dt = new DataTable();
00730             dAdapt.Fill(dt);
00731             return dt;
00732         }
00733         private static bool containsFK(string table, string fk)
00734         {
00735             string selStr = "SELECT * FROM \"" + table + "\"";
00736             SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString);
00737             SqlDataAdapter dAdapt = new SqlDataAdapter(selStr, con);
00738             DataTable dt = new DataTable();
00739             dAdapt.Fill(dt);
00740             if (dt.Columns.Contains(fk))
00741                 return true;
00742             else return false;
00743         }
00744         private static int getRowID(RAIS.Common.TableManagement.Table table, RAIS.Common.TableManagement.Table relTable, int rowID)
00745         {
00746             using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString))
00747             {
00748                 if (containsFK(relTable.Name, table.ForeignKeyFieldName))
00749                 {
00750                     string selStr = "SELECT \"" + relTable.PrimaryKeyFieldName + "\" FROM \"" + relTable.Name + "\" WHERE \"" + table.ForeignKeyFieldName + "\" = " + rowID;
00751 
00752                     SqlDataAdapter dAdapt = new SqlDataAdapter(selStr, con);
00753                     DataTable dt = new DataTable();
00754                     dAdapt.Fill(dt);
00755                     if (dt.Rows.Count > 0)
00756                         return Convert.ToInt32(dt.Rows[dt.Rows.Count - 1].ItemArray[dt.Columns.IndexOf(relTable.PrimaryKeyFieldName)]);
00757                     else
00758                         return -1;
00759                 }
00760                 else if (containsFK(table.Name, relTable.ForeignKeyFieldName))
00761                 {
00762                     string selStr = "SELECT \"" + relTable.ForeignKeyFieldName + "\" FROM \"" + table.Name + "\" WHERE \"" + table.PrimaryKeyFieldName + "\" = " + rowID;
00763                     SqlDataAdapter dAdapt = new SqlDataAdapter(selStr, con);
00764                     DataTable dt = new DataTable();
00765                     dAdapt.Fill(dt);
00766                     if (dt.Rows.Count > 0)
00767                         return Convert.ToInt32(dt.Rows[dt.Rows.Count - 1].ItemArray[dt.Columns.IndexOf(relTable.ForeignKeyFieldName)]);
00768                     else
00769                         return -1;
00770                 }
00771                 else
00772                     return -1;
00773             }
00774         }
00775         #endregion
00776     }
00777 }